package com.tgb.lk.autossh;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;
import java.util.Map.Entry;

import javax.sql.rowset.CachedRowSet;

import com.tgb.lk.annotation.AutoBean;
import com.tgb.lk.annotation.AutoField;
import com.tgb.lk.config.Config;
import com.tgb.lk.config.ConfigXmlUtil;
import com.tgb.lk.table.DBManager;
import com.tgb.lk.table.Log;
import com.tgb.lk.util.AppConstants;
import com.tgb.lk.util.ClassFieldUtil;
import com.tgb.lk.util.ClassLoaderUtil;
import com.tgb.lk.util.ExcelUtil;
import com.tgb.lk.util.FileUtil;
import com.tgb.lk.util.ManageClassLoader;

public class DBExcelDataUtil {
	public static void db2Excel() {
		Map<Class<?>, List<Object>> map = getAllDataFromDB();
		for (Entry<Class<?>, List<Object>> entry : map.entrySet()) {
			Class<?> clazz = entry.getKey();
			List<Object> value = entry.getValue();
			if (clazz.isAnnotationPresent(AutoBean.class)) {
				AutoBean autoBean = (AutoBean) clazz
						.getAnnotation(AutoBean.class);
				String tableName = autoBean.table();

				ExcelUtil util = new ExcelUtil(clazz);
				String path = FileUtil.makeFilePath(AppConstants.BASE_PATH
						+ "dest-excel/" + tableName + ".xls");
				FileOutputStream out = null;
				try {
					out = new FileOutputStream(path);
				} catch (FileNotFoundException e) {
					Log.log(e);
					e.printStackTrace();
				}

				util.exportExcel(value, tableName, out);
				Log.log("导出成功：" + path, 3);
			}
		}
	}

	public static void excel2Db() {
		Config config = new ConfigXmlUtil().getConfig();
		if (config != null) {
			System.out.println("config info:" + config);
			List<Class<?>> classes = new ArrayList<Class<?>>();
			try {
				// 将配置在libs文件夹中的jar加载到环境中.
				List<String> jarFiles = FileUtil.getAllFiles(config
						.getLibsDir(), null);
				for (String file : jarFiles) {
					if (file.toLowerCase().endsWith(".jar")) {
						ClassLoaderUtil.addClassPath(file);
					}
				}
				// 将class加载到classLoader中,支持修改后重新加载class
				String path = config.getBeansDir();
				ManageClassLoader mc = new ManageClassLoader();
				classes = mc.loadClass(path);
				Log.log("beans:" + classes, 4);
			} catch (Exception e) {
				Log.log(e);
				e.printStackTrace();
			}
			for (Class<?> clazz : classes) {
				List<Object> clsInsList = getDataFromExcel(clazz);
				if (clsInsList != null && clsInsList.size() > 0) {
					data2Db(clazz, clsInsList);// 导入数据
				}
			}
		}
	}

	public static void data2Db(Class<?> clazz, List<Object> list) {
		if (clazz.isAnnotationPresent(AutoBean.class)) {
			AutoBean autoBean = (AutoBean) clazz.getAnnotation(AutoBean.class);
			if ("".equals(autoBean.table().trim())) {
				return;// 没有配置table属性时退出本次循环
			}
			String tableName = autoBean.table();
			List<Field> fields = new ArrayList<Field>();
			fields = ClassFieldUtil.getMappedField(clazz, fields,
					AutoField.class);
			if (fields.size() < 1) {
				return;
			}
			for (Object obj : list) {
				StringBuffer strField = new StringBuffer("insert into ")
						.append(tableName).append("(");
				StringBuffer strValue = new StringBuffer(" values(");
				Object[] parms = new Object[fields.size()];
				for (int i = 0; i < fields.size(); i++) {
					Field field = fields.get(i);
					if (field.isAnnotationPresent(AutoField.class)) {
						AutoField autoField = field
								.getAnnotation(AutoField.class);
						String columnName = autoField.column();
						field.setAccessible(true);
						try {
							strField.append(columnName).append(",");
							strValue.append("?,");
							parms[i] = field.get(obj);
						} catch (IllegalArgumentException e) {
							Log.log(e);
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							Log.log(e);
							e.printStackTrace();
						}
					}
				}
				strField.deleteCharAt(strField.length() - 1).append(")");
				strValue.deleteCharAt(strValue.length() - 1).append(")");
				String sql = strField.toString() + strValue.toString();
				DBManager.getInstance().executeUpdate(sql, parms);
			}
		}
	}

	public static List<Object> getDataFromExcel(Class<?> clazz) {
		List<Object> clsInsList = new ArrayList<Object>();
		if (clazz.isAnnotationPresent(AutoBean.class)) {
			AutoBean autoBean = (AutoBean) clazz.getAnnotation(AutoBean.class);
			if ("".equals(autoBean.table().trim())) {
				return null;// 没有配置table属性时退出本次循环
			}
			String tableName = autoBean.table();
			ExcelUtil util = new ExcelUtil(clazz);
			String path = FileUtil.makeFilePath(AppConstants.BASE_PATH
					+ "dest-excel/" + tableName + ".xls");
			InputStream input;
			try {
				input = new FileInputStream(path);
				clsInsList = util.importExcel(autoBean.table(), input);
			} catch (FileNotFoundException e) {
				Log.log(e);
				e.printStackTrace();
			}
		}
		return clsInsList;
	}

	public static Map<Class<?>, List<Object>> getAllDataFromDB() {
		Map<Class<?>, List<Object>> retMap = new LinkedHashMap<Class<?>, List<Object>>();// 定义最终返回的map
		Config config = new ConfigXmlUtil().getConfig();
		if (config != null) {
			System.out.println("config info:" + config);
			List<Class<?>> classes = new ArrayList<Class<?>>();
			try {
				// 将配置在libs文件夹中的jar加载到环境中.
				List<String> jarFiles = FileUtil.getAllFiles(config
						.getLibsDir(), null);
				for (String file : jarFiles) {
					if (file.toLowerCase().endsWith(".jar")) {
						ClassLoaderUtil.addClassPath(file);
					}
				}
				// 将class加载到classLoader中,支持修改后重新加载class
				String path = config.getBeansDir();
				ManageClassLoader mc = new ManageClassLoader();
				classes = mc.loadClass(path);
				Log.log("beans:" + classes, 4);
			} catch (Exception e) {
				Log.log(e);
				e.printStackTrace();
			}
			for (Class<?> clazz : classes) {
				List<Object> clsInsList = getDataFromDB(clazz);
				if (clsInsList != null) {
					retMap.put(clazz, clsInsList);// 将该对象的list加入到最终的list中.
				}
			}
		}
		return retMap;
	}

	public static List<Object> getDataFromDB(Class<?> clazz) {
		List<Object> clsInsList = new ArrayList<Object>();
		if (clazz.isAnnotationPresent(AutoBean.class)) {
			AutoBean autoBean = (AutoBean) clazz.getAnnotation(AutoBean.class);
			if ("".equals(autoBean.table().trim())) {
				return null;// 没有配置table属性时退出本次循环
			}
			Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
			Map<String, Field> fieldsMap = new HashMap<String, Field>(64);// 定义一个map用于存放列名和field.
			for (Field field : allFields) {
				// 将有注解的field存放到map中.
				if (field.isAnnotationPresent(AutoField.class)) {
					AutoField attr = field.getAnnotation(AutoField.class);
					if ("".equals(attr.column().trim())) {
						continue;// 没有配置column字段的不导出.
					}
					field.setAccessible(true);// 设置类的私有字段属性可访问.
					fieldsMap.put(attr.column().trim(), field);
				}
			}
			if (fieldsMap.size() < 1) {
				return null;// 所有的属性都没配置column字段退出本次循环.
			}

			String table = autoBean.table().trim();
			CachedRowSet crs = DBManager.getInstance().executeQuery(
					"select * from " + table, null);// 读取数据

			try {
				// new出对象并设值
				while (crs.next()) {
					Object obj;
					obj = clazz.newInstance();
					for (Entry<String, Field> entry : fieldsMap.entrySet()) {
						String key = entry.getKey();
						Field field = entry.getValue();
						Class<?> fieldType = field.getType();
						if (String.class == fieldType) {
							field.set(obj, crs.getString(key));
						} else if (Integer.TYPE == fieldType) {
							field.set(obj, crs.getInt(key));
						} else {
							field.set(obj, crs.getObject(key));
						}
					}
					clsInsList.add(obj);// 将实体new出的对象加入到list中.
				}
			} catch (IllegalArgumentException e) {
				Log.log(e);
				e.printStackTrace();
			} catch (SQLException e) {
				Log.log(e);
				e.printStackTrace();
			} catch (InstantiationException e) {
				Log.log(e);
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				Log.log(e);
				e.printStackTrace();
			}

		}
		return clsInsList;
	}
}
